package com.example.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.UUID;
import java.util.Vector;

import javax.jws.soap.SOAPBinding.Use;
import com.example.dao2.UserDao;
import com.example.dao2.VehicleDao;
import com.example.debug.gson.DaoDebug;
import com.example.entity.User;
import com.example.entity.Vehicle;
import com.example.entity.VehicleFaultInfo;
import com.example.utils.DButil;
import com.google.gson.Gson;
import com.mysql.jdbc.PreparedStatement;

/**
 * Vehicle表Dao层的实现类
 * 
 * @author Roy
 * 
 */
public class VehicleDaoImpl implements VehicleDao {
	/**
	 * 向vehicleinfotbl表中插入一条数据
	 */
	public Boolean insert(VehicleFaultInfo v) {
		String sql = "insert into vehiclerecordtbl(id,name,idno,vno,createTime,faultRecord,penalty,dealid)values(?,?,?,?,?,?,?,?)";
		DButil dbUtil = new DButil();
		Connection connect = dbUtil.openConnection();
		try {
			java.sql.PreparedStatement pstmt = connect.prepareStatement(sql);
			pstmt.setString(1, v.getId());
			pstmt.setString(2, v.getName());
			pstmt.setString(3, v.getIdno());
			pstmt.setString(4, v.getVno());
			pstmt.setString(5, v.getCreatetime());
			pstmt.setString(6, v.getFaultRecord());
			pstmt.setString(7, v.getPenalty());
			pstmt.setString(8, v.getDealid());
			// ResultSet rs=pstmt.executeQuery();
			if (pstmt.executeUpdate() > 0) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("insert into vehicleinfotbl has error: "
					+ e.toString());
			e.printStackTrace();
		} finally {
			dbUtil.closeConn(connect);
		}
		// TODO Auto-generated method stub
		return false;
	}

	/**
	 * 查询车辆
	 */
	public String query(Vehicle v) {
		ResultSet rs = null;
		ResultSet rs2 = null;
		String search = "";
		DButil dbUtil = new DButil();
		Connection connect = dbUtil.openConnection();
		try {
			/**
			 * 以下是大篇幅的拼接sql语句
			 */
			if (v.getVno() == null || v.getVno().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "   AND A.vno =?";
			}
			if (v.getIdno() == null || v.getIdno().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND A.idno =? ";
			}
			if (v.getType() == null || v.getType().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND A.type LIKE ? ";
			}
			if (v.getProduct() == null || v.getProduct().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += " AND  A.product LIKE ?";
			}
			if (v.getColor() == null || v.getColor().equals("")) {
				search += "  AND 1 =? ";
			} else {
				search += "  AND  A.color LIKE ?  ";
			}
			String query = "select * from vehicletbl A WHERE  1 = 1 " + search;
			java.sql.PreparedStatement pstmt = connect.prepareStatement(query);

			System.out.println("query是------" + query);
			/**
			 * 以下是拼接查询条件
			 */
			if (v.getVno() == null || v.getVno().equals("")) {
				pstmt.setString(1, "1");
			} else {
				pstmt.setString(1, v.getVno());
			}
			if (v.getIdno() == null || v.getIdno().equals("")) {
				pstmt.setString(2, "1");
			} else {
				pstmt.setString(2, v.getIdno());
			}
			if (v.getType() == null || v.getType().equals("")) {
				pstmt.setString(3, "1");
			} else {
				pstmt.setString(3, "%" + v.getType() + "%");
			}
			if (v.getProduct() == null || v.getProduct().equals("")) {
				pstmt.setString(4, "1");
			} else {
				pstmt.setString(4, "%" + v.getProduct() + "%");
			}
			if (v.getColor() == null || v.getColor().equals("")) {
				pstmt.setString(5, "1");
			} else {
				pstmt.setString(5, "%" + v.getColor() + "%");
			}
			rs = pstmt.executeQuery();
			// return rs;
			ArrayList<Vehicle> vs = new ArrayList<Vehicle>();		
			while (rs.next()) {
				// 如果fault中有两条记录，导致查询出来的vehicle也存在两条记录，这是多余的,无法解决！
				Vehicle v2 = new Vehicle();
				VehicleFaultInfo vfi = new VehicleFaultInfo();
				v2.setId(rs.getObject(1).toString());
				v2.setVno(rs.getObject(2).toString());
				v2.setProduct(rs.getObject(3).toString());
				v2.setIdno(rs.getObject(4).toString());
				v2.setType(rs.getObject(5).toString());
				v2.setCreateTime(rs.getObject(6).toString());
				v2.setColor(rs.getObject(7).toString());
				if (rs.getObject(8) != null) {
					v2.setPicUrl(rs.getObject(8).toString());
				}
				vs.add(v2);
			}

			System.out.println("vs中vehcile的个数是" + vs.size());
			for (int i = 0; i < vs.size(); i++) {
				ArrayList<VehicleFaultInfo> vcfis = new ArrayList<VehicleFaultInfo>();
				Vehicle vc = new Vehicle();
				vc = vs.get(i);
				String vno = vc.getVno();
				String queryVehicleInfo = "SELECT * FROM vehiclerecordtbl WHERE vno=?";
				System.out.println("query --" + query);
				java.sql.PreparedStatement pstmt2 = connect
						.prepareStatement(queryVehicleInfo);
				pstmt2.setString(1, vno);
				rs2 = pstmt2.executeQuery();
				while (rs2.next()) {
					VehicleFaultInfo vfi = new VehicleFaultInfo();
					vfi.setId(rs2.getObject(1).toString());
					vfi.setName(rs2.getObject(2).toString());
					vfi.setIdno(rs2.getObject(3).toString());
					vfi.setVno(rs2.getObject(4).toString());
					vfi.setCreatetime(rs2.getObject(5).toString());
					vfi.setFaultRecord(rs2.getObject(6).toString());
					vfi.setPenalty(rs2.getObject(7).toString());
					vfi.setDealid(rs2.getObject(8).toString());
					vcfis.add(vfi);
				}
				vc.setRecords(vcfis);
			}
			System.out.println("vs is ----" + vs.toString());
			rs.last();
			rs2.last();
			int rowCount = rs.getRow(); // 获得rs的总行数
			int rowCount2 = rs2.getRow(); // 获得rs的总行数
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			ResultSetMetaData rsmd2 = rs2.getMetaData();
			int columnCount2 = rsmd2.getColumnCount();
			Gson gson = new Gson();
			String s = gson.toJson(vs);
			System.out.println(s);
			System.out.println("rs行数是" + rowCount + "rs的列数是" + columnCount);
			System.out.println("rs2行数是" + rowCount2 + "rs的列数是" + columnCount2);
			return s;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("query from vehicleinfotbl has error: "
					+ e.toString());
			e.printStackTrace();

		} finally {
			dbUtil.closeConn(connect);

		}
		// TODO Auto-generated method stub
		return "";
	}

//	public static void main(String[] args) {
//
//		Vehicle v = new Vehicle();
//		v.setColor("黑色");
//		//v.setType("卡车");
//	//	v.setVno("鲁E7887");
//		v.setProduct("大众");
//		String result = new VehicleDaoImpl().query(v);
//		System.out.println("----result is ---" + result);
//
//	}
}
